﻿using System;
using System.Data;
using System.Data.SqlClient;
using UniversityStudentManagemnetSystem.DAO.DAL;

namespace UniversityStudentManagemnetSystem.DAO.Gateway
{
    public class CourseGateway:DAO.Gateway.Gateway
    {
        public bool CourseSave(Course course)
        {
            try
            {
                Connection.Open();
                string insertQuery =
                    string.Format(
                    "insert into t_Course values(@Code,@Credit,@CourseName,@Description,@DepartmentId,@SemesterId)");
                Command.CommandText = insertQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@Code", SqlDbType.VarChar);
                Command.Parameters["@Code"].Value = course.Code;
                Command.Parameters.Add("@Credit", SqlDbType.VarChar);
                Command.Parameters["@Credit"].Value = course.Credit;
                Command.Parameters.Add("@CourseName", SqlDbType.VarChar);
                Command.Parameters["@CourseName"].Value = course.CourseName;
                Command.Parameters.Add("@Description", SqlDbType.VarChar);
                Command.Parameters["@Description"].Value = course.Description;
                Command.Parameters.Add("@DepartmentId", SqlDbType.Int);
                Command.Parameters["@DepartmentId"].Value = course.Department.DeptId;
                Command.Parameters.Add("@SemesterId", SqlDbType.Int);
                Command.Parameters["@SemesterId"].Value = course.Semester.Id;
                Command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception("Save couldn't performed due to system error!", exception);
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return true;

        }

        public bool GetHasThisCourseCode(string code)
        {
            bool hasRows = false;
            try
            {

                Connection.Open();
                string selectQuery = string.Format("select Code from t_Course where Code=@code");
                Command.CommandText = selectQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@code", SqlDbType.VarChar);
                Command.Parameters["@code"].Value = code;
                SqlDataReader sqlDataReader = Command.ExecuteReader();
                
                if (sqlDataReader != null)
                {
                    hasRows = sqlDataReader.HasRows;

                }
                sqlDataReader.Close();
              
            }
           catch(FormatException exception)
                {
                    throw new Exception("Input was not in Correct Type!",exception);
                }
            catch (Exception exception)
            {
                
                throw new Exception("Sorry!System Error!Please Try Again! ",exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            
            return hasRows;
        }

        public bool GetHasThisCourseName(string courseName)
        {
            bool hasRows = false;
            try
            {
                Connection.Open();
                string selectQuery = String.Format("select CourseName from t_Course where CourseName=@courseName");
                Command.CommandText = selectQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@courseName", SqlDbType.VarChar);
                Command.Parameters["@courseName"].Value = courseName;
                SqlDataReader sqlDataReader = Command.ExecuteReader();
               
                if (sqlDataReader != null)
                {
                    hasRows = sqlDataReader.HasRows;
                }
                sqlDataReader.Close();
                
            }
                catch(FormatException exception)
                {
                    throw new Exception("Input was not in Correct Type!",exception);
                }
            catch (Exception exception)
            {

                throw new Exception("Sorry!System Error!Please Try Again!",exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return hasRows;
        }

        public DataTable GetCourses()
        {
            string selectQuery = string.Format("select * from t_Course");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
            DataSet myDataset = new DataSet();
            sqlDataAdapter.Fill(myDataset, "CourseTable");
            DataTable myDataTable = myDataset.Tables["CourseTable"];
            return myDataTable;
        }
        public DataTable GetAllCourseFor(int selectedDepartmentId)
        {
            Command.CommandText = "SELECT * FROM t_Course WHERE DepartmentId = @id";
            Command.Parameters.Add("@id", SqlDbType.Int).Value = selectedDepartmentId;
            queryString = GetQueryString(Command);

            DataTable courseDataTable = null;
            try
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, Connection.ConnectionString);
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet);
                courseDataTable = dataSet.Tables[0];
                nbrOfTableRows = courseDataTable.Rows.Count;
            }
            catch (Exception exception)
            {
                throw new Exception("Database Error", exception);
            }
            return courseDataTable;
        }
    }
}